﻿/*---------------------------------------------------------------------------------
-----------------***DaLianQianYi***-----------------------
-----------------作者：大连仟亿科技------------------------------------------------------
-----------------联系作者：www.qianyikeji.com----------------------------------
-----------------时间：2024/7/26 11:30:38------------------------------------------
----------------------------------------------------------------------------------*/


using Donal.Xms.Common;
using Donal.Xms.DBUtility;
using System;
using System.Collections.Generic;
using System.Reflection;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;

namespace Donal.Xms.DAL
{
    /// <summary>
    /// 数据访问类
    /// </summary>
    public partial class products
    {
        private string databaseprefix; //数据库表名前缀
        public products(string _databaseprefix)
        {
            databaseprefix = _databaseprefix;
        }

        #region 基本方法================================
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from  " + databaseprefix + "products");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                        new SqlParameter("@id", SqlDbType.Int,10)};
            parameters[0].Value = id;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(Model.products model)
        {
            using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        #region 添加主表数据====================
                        StringBuilder strSql = new StringBuilder();
                        StringBuilder str1 = new StringBuilder();//数据字段
                        StringBuilder str2 = new StringBuilder();//数据参数
                                                                 //利用反射获得属性的所有公共属性
                        PropertyInfo[] pros = model.GetType().GetProperties();
                        List<SqlParameter> paras = new List<SqlParameter>();
                        strSql.Append("insert into " + databaseprefix + "products(");
                        foreach (PropertyInfo pi in pros)
                        {
                            //如果不是主键则追加sql字符串
                            if (!pi.Name.Equals("id") && !typeof(System.Collections.IList).IsAssignableFrom(pi.PropertyType))
                            {
                                //判断属性值是否为空
                                if (pi.GetValue(model, null) != null)
                                {
                                    str1.Append(pi.Name + ",");//拼接字段
                                    str2.Append("@" + pi.Name + ",");//声明参数
                                    paras.Add(new SqlParameter("@" + pi.Name, pi.GetValue(model, null)));//对参数赋值
                                }
                            }
                        }
                        strSql.Append(str1.ToString().Trim(','));
                        strSql.Append(") values (");
                        strSql.Append(str2.ToString().Trim(','));
                        strSql.Append(") ");
                        strSql.Append(";select @@IDENTITY;");
                        object obj = DbHelperSQL.GetSingle(conn, trans, strSql.ToString(), paras.ToArray());//带事务
                        model.id = Convert.ToInt32(obj);//插入后赋值
                        #endregion

                        #region 添加相册====================
                        if (model.products_albums != null)
                        {
                            new DAL.products_albums(databaseprefix).Add(conn, trans, model.products_albums, model.id);
                        }
                        #endregion

                        #region 添加附件====================
                        if (model.products_attaches != null)
                        {
                            new DAL.products_attach(databaseprefix).Add(conn, trans, model.products_attaches, model.id);
                        }
                        #endregion

                        #region 产品规格===========================
                        if (model.products_fields != null)
                        {
                            StringBuilder strSql3;
                            foreach (Model.products_field modelt in model.products_fields)
                            {
                                strSql3 = new StringBuilder();
                                strSql3.Append("insert into dt_products_field(");
                                strSql3.Append("products_id,field_id,display,sort_id)");
                                strSql3.Append(" values (");
                                strSql3.Append("@products_id,@field_id,@display,@sort_id)");
                                SqlParameter[] parameters3 = {
                                            new SqlParameter("@products_id", SqlDbType.Int,4),
                                            new SqlParameter("@field_id", SqlDbType.Int,4),
                                            new SqlParameter("@display", SqlDbType.Int,4),
                                            new SqlParameter("@sort_id", SqlDbType.Int,4),};
                                parameters3[0].Value = model.id;//产品的ID
                                parameters3[1].Value = modelt.field_id;
                                parameters3[2].Value = modelt.display;
                                parameters3[3].Value = modelt.sort_id;
                                DbHelperSQL.ExecuteSql(conn, trans, strSql3.ToString(), parameters3);
                            }
                        }
                        #endregion

                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        return 0;
                    }
                }
            }
            return model.id;
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Model.products model)
        {
            using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        #region 修改主表数据==========================
                        StringBuilder strSql = new StringBuilder();
                        StringBuilder str1 = new StringBuilder();
                        //利用反射获得属性的所有公共属性
                        PropertyInfo[] pros = model.GetType().GetProperties();
                        List<SqlParameter> paras = new List<SqlParameter>();
                        strSql.Append("update  " + databaseprefix + "products set ");
                        foreach (PropertyInfo pi in pros)
                        {
                            //如果不是主键则追加sql字符串
                            if (!pi.Name.Equals("id") && !typeof(System.Collections.IList).IsAssignableFrom(pi.PropertyType))
                            {
                                //判断属性值是否为空
                                if (pi.GetValue(model, null) != null)
                                {
                                    str1.Append(pi.Name + "=@" + pi.Name + ",");//声明参数
                                    paras.Add(new SqlParameter("@" + pi.Name, pi.GetValue(model, null)));//对参数赋值
                                }
                            }
                        }
                        strSql.Append(str1.ToString().Trim(','));
                        strSql.Append(" where id=@id ");
                        paras.Add(new SqlParameter("@id", model.id));
                        DbHelperSQL.ExecuteSql(conn, trans, strSql.ToString(), paras.ToArray());
                        #endregion

                        #region 修改相册==========================
                        //删除/添加/修改相册图片
                        new DAL.products_albums(databaseprefix).Update(conn, trans, model.products_albums, model.id);
                        #endregion

                        #region 修改附件==========================
                        //删除/添加/修改附件
                        new DAL.products_attach(databaseprefix).Update(conn, trans, model.products_attaches, model.id);
                        #endregion

                        #region 产品规格===========================                                               

                        //添加/修改产品规格
                        if (model.products_fields != null)
                        {
                            string ids = "";
                            string field_id = "";
                            foreach (Model.products_field modelt in model.products_fields)
                            {
                                field_id += modelt.field_id + ",";
                                ids += modelt.id + ",";
                            }

                            if (ids != "")
                            {
                                //删除已删除的产品规格
                                DbHelperSQL.ExecuteSql(conn, trans, "delete from dt_products_field where id not in(" + ids.Substring(0, ids.Length - 1) + ") and products_id=" + model.id);

                            }

                            StringBuilder strSql3;
                            foreach (Model.products_field modelt in model.products_fields)
                            {
                                strSql3 = new StringBuilder();
                                if (modelt.id > 0)
                                {
                                    strSql3.Append("update dt_products_field set ");
                                    strSql3.Append("products_id=@products_id,");
                                    strSql3.Append("field_id=@field_id,");
                                    strSql3.Append("display=@display,");
                                    strSql3.Append("sort_id=@sort_id");
                                    strSql3.Append(" where id=@id");
                                    SqlParameter[] parameters3 = {
                                            new SqlParameter("@products_id", SqlDbType.Int,4),
                                            new SqlParameter("@field_id", SqlDbType.Int,4),
                                            new SqlParameter("@display", SqlDbType.Int,4),
                                            new SqlParameter("@sort_id", SqlDbType.Int,4),
                                            new SqlParameter("@id", SqlDbType.Int,4)};
                                    parameters3[0].Value = modelt.products_id;
                                    parameters3[1].Value = modelt.field_id;
                                    parameters3[2].Value = modelt.display;
                                    parameters3[3].Value = modelt.sort_id;
                                    parameters3[4].Value = modelt.id;
                                    DbHelperSQL.ExecuteSql(conn, trans, strSql3.ToString(), parameters3);
                                }
                                else
                                {
                                    strSql3.Append("insert into dt_products_field(");
                                    strSql3.Append("products_id,field_id,display,sort_id)");
                                    strSql3.Append(" values (");
                                    strSql3.Append("@products_id,@field_id,@display,@sort_id)");
                                    SqlParameter[] parameters3 = {
                                            new SqlParameter("@products_id", SqlDbType.Int,4),
                                            new SqlParameter("@field_id", SqlDbType.Int,4),
                                            new SqlParameter("@display", SqlDbType.Int,4),
                                            new SqlParameter("@sort_id", SqlDbType.Int,4),};
                                    parameters3[0].Value = model.id;//产品的ID
                                    parameters3[1].Value = modelt.field_id;
                                    parameters3[2].Value = modelt.display;
                                    parameters3[3].Value = modelt.sort_id;
                                    DbHelperSQL.ExecuteSql(conn, trans, strSql3.ToString(), parameters3);
                                }
                            }

                            #region 将这个产品下的所有规格的扩展字段重置，删除不存在的扩展，如果有新扩展字段添加新的扩展

                            //这里是该产品所有扩展字段的名，遍历下属所有规格的扩展字段，将不在这里的扩展删除。
                            string notInFieldName = "";

                            ///找出该产品的扩展字段
                            DataSet _filedDataSet = new DAL.products_attribute_field(databaseprefix).GetList(0, "id in(" + field_id.Substring(0, field_id.Length - 1) + ")", "id");

                            ///以扩展字段名做为Key，生成插入Sql语句。
                            Dictionary<string, string> insertSqlDic = new Dictionary<string, string>();
                            if (Utils.CheckDataSet(_filedDataSet))
                            {
                                foreach (DataRow dr in _filedDataSet.Tables[0].Rows)
                                {
                                    notInFieldName += "'" + dr["name"] + "',";

                                    if (!insertSqlDic.ContainsKey(dr["name"].ToString()))
                                    {
                                        insertSqlDic.Add(dr["name"].ToString(), "INSERT INTO [dbo].[dt_products_goods_filed]([product_goods_id],[field_name],[field_value],[field_title]) VALUES({product_goods_id}, '" + dr["name"] + "','" + dr["default_value"] + "','" + dr["title"] + "');");
                                    }
                                }
                            }

                            //遍历所有下属规格
                            foreach (Model.products_goods pg in model.products_goods)
                            {
                                ///删除规格都不在存在的扩展字段
                                DbHelperSQL.ExecuteSql(conn, trans, "delete from dt_products_goods_filed where field_name not in(" + notInFieldName.Substring(0, notInFieldName.Length - 1) + ") and product_goods_id = " + pg.id);

                                ///创建新的扩展
                                if (insertSqlDic.Count > 0)
                                {
                                    foreach (Model.products_goods_filed pgf in pg.fields)
                                    {
                                        foreach (KeyValuePair<string, string> kvp in insertSqlDic)
                                        {
                                            //添加新的扩展字段
                                            if (kvp.Key != pgf.field_name)
                                            {
                                                DbHelperSQL.ExecuteSql(conn, trans, kvp.Value.Replace("{product_goods_id}", pg.id.ToString()));
                                            }
                                        }
                                    }
                                }
                            }
                            #endregion

                        }
                        #endregion

                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        return false;
                    }
                }
            }
            return true;
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int products_id)
        {
            //取得相册MODEL
            List<Model.products_albums> albumsList = new DAL.products_albums(databaseprefix).GetList(products_id);
            //取得附件MODEL
            List<Model.products_attach> attachList = new DAL.products_attach(databaseprefix).GetList(products_id);

            //删除图片相册
            StringBuilder strSql2 = new StringBuilder();
            strSql2.Append("delete from " + databaseprefix + "products_albums");
            strSql2.Append(" where products_id=@products_id");
            SqlParameter[] parameters2 = {
                    new SqlParameter("@products_id", SqlDbType.Int,4)};

            parameters2[0].Value = products_id;
            List<CommandInfo> sqllist = new List<CommandInfo>();
            CommandInfo cmd = new CommandInfo(strSql2.ToString(), parameters2);
            sqllist.Add(cmd);

            //删除附件
            StringBuilder strSql3 = new StringBuilder();
            strSql3.Append("delete from " + databaseprefix + "products_attach");
            strSql3.Append(" where products_id=@products_id");
            SqlParameter[] parameters3 = {
                    new SqlParameter("@products_id", SqlDbType.Int,4)};
            parameters3[0].Value = products_id;
            cmd = new CommandInfo(strSql3.ToString(), parameters3);
            sqllist.Add(cmd);

            //删除产品规格
            StringBuilder strSql4 = new StringBuilder();
            strSql4.Append("delete from dt_products_field ");
            strSql4.Append(" where products_id=@products_id");
            SqlParameter[] parameters4 = {
                    new SqlParameter("@products_id", SqlDbType.Int,4)};
            parameters4[0].Value = products_id;

            cmd = new CommandInfo(strSql4.ToString(), parameters4);
            sqllist.Add(cmd);

            //删除主表
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from dt_products ");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                    new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = products_id;
            cmd = new CommandInfo(strSql.ToString(), parameters);
            sqllist.Add(cmd);

            int rowsAffected = DbHelperSQL.ExecuteSqlTran(sqllist);
            if (rowsAffected > 0)
            {
                new DAL.products_albums(databaseprefix).DeleteFile(albumsList); //删除图片
                new DAL.products_attach(databaseprefix).DeleteFile(attachList); //删除附件
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Model.products GetModel(int id)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select top 1 * from " + databaseprefix + "products");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                        new SqlParameter("@id", SqlDbType.Int,10)};
            parameters[0].Value = id;
            DataTable dt = DbHelperSQL.Query(strSql.ToString(), parameters).Tables[0];

            if (dt.Rows.Count > 0)
            {
                return DataRowToModel(dt.Rows[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" * ");
            strSql.Append(" FROM " + databaseprefix + "products");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得查询分页数据
        /// </summary>
        public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * FROM " + databaseprefix + "products");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
            return DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder));
        }
        #endregion

        #region 扩展方法================================
        /// <summary>
        /// 返回指定列
        /// </summary>
        public string GetValue(int id, string fileName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 " + fileName + " from " + databaseprefix + "products");
            strSql.Append(" where id=" + id);
            string title = Convert.ToString(DbHelperSQL.GetSingle(strSql.ToString()));
            if (string.IsNullOrEmpty(title))
            {
                return "";
            }
            return title;
        }

        /// <summary>
        /// 修改一列数据
        /// </summary>
        public bool UpdateField(int id, string strValue)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update " + databaseprefix + "products set " + strValue);
            strSql.Append(" where id=" + id);
            return DbHelperSQL.ExecuteSql(strSql.ToString()) > 0;
        }

        /// <summary>
        /// 将对象转换实体
        /// </summary>
        public Model.products DataRowToModel(DataRow row)
        {
            Model.products model = new Model.products();
            if (row != null)
            {
                //利用反射获得属性的所有公共属性
                Type modelType = model.GetType();
                for (int i = 0; i < row.Table.Columns.Count; i++)
                {
                    //查找实体是否存在列表相同的公共属性
                    PropertyInfo proInfo = modelType.GetProperty(row.Table.Columns[i].ColumnName);
                    if (proInfo != null && row[i] != DBNull.Value)
                    {
                        proInfo.SetValue(model, row[i], null);//用索引值设置属性值
                    }
                }

                //相册信息
                model.products_albums = new DAL.products_albums(databaseprefix).GetList(model.id);
                //附件信息
                model.products_attaches = new DAL.products_attach(databaseprefix).GetList(model.id);
                //商品价格
                model.products_goods = new DAL.products_goods(databaseprefix).GetList(model.id);
            }
            return model;
        }
        #endregion
    }
}

